#include <stdlib.h>
#include <iostream>
#include <sstream>
#include <string>
#include <QString>
#include <QTime>
#include <mysql/mysql.h>
#include <list>

#include "station_mysql.h"
#include "DataStruct.h"

using namespace std;

MYSQL_ROW* queryTrainStation_mysql(char *num);

static MYSQL my_connection;
static int dbconnected = 0;

int database_start(char *name, char *pwd) {
    if (dbconnected)
        return 1;

    mysql_init(&my_connection);
    if (!mysql_real_connect(&my_connection, "localhost", name, pwd, "train", 3306, NULL, 0)) {
        cout << "Database connection failure: " << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }
    dbconnected = 1;
    return 1;

}

void database_end() {
    if(dbconnected)
        mysql_close(&my_connection);
    dbconnected = 0;
}


/*****************
 *station
*****************/

int addStation_mysql(char *name) {
    int res;
    string sql;
    string sname = name;

    if (name == NULL) {
        cout<< "Station name cannot be empty!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "INSERT INTO station(name) VALUES(\"" + sname + "\")";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Insert error" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Insert " << name << " success!" << endl;
    return 1;
}

int deleteStation_mysql(char *name) {
    int res;
    string sql;
    string sname = name;

    if (name == NULL) {
        cout<< "Station name cannot be empty!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "DELETE FROM station WHERE name = \"" + sname + "\"";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Delete error (station)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Delete " << name << " success!" << endl;
    return 1;
}

int deleteStation_mysql(int id) {
    int res;
    string sql;
    string sID;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (!dbconnected)
        return 0;


    sql = "DELETE FROM station WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Delete error (station)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Delete " << id << " success!" << endl;
    return 1;
}

int modifyStation_mysql(int id, char *newName) {
    int res;
    string sql;
    string snewName = newName;
    string sID;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (newName == NULL) {
        cout<< "Station name cannot be empty!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "UPDATE station SET name = \"" + snewName + "\" WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Update error (station)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Change " << "to " << id << " " << newName << " success!" << endl;
    return 1;
}

int queryStation_mysql(char *name) {
    int res;
    int id = 0;
    string sql;
    string sname = name;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;

    if (name == NULL) {
        cout<< "Station name cannot be empty!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "SELECT * FROM station WHERE name = \"" + sname + "\"";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (station)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
//                cout << "ID  " << "Name " <<endl;
//                while (sqlRow = mysql_fetch_row(res_ptr)) {
//                    cout << sqlRow[0] << "  " << sqlRow[1] << endl;
//                }
                sqlRow = mysql_fetch_row(res_ptr);
                id = atoi(sqlRow[0]);
            } else {
                cout << "Cannot find the station name!" << endl;
            }
            mysql_free_result(res_ptr);
        }
    }

    return id;
}

char *queryStation_mysql(int id) {
    int res;
    char *ch = NULL;
    string sql;
    string sID;
    MYSQL_RES *res_p;
    MYSQL_ROW sql_row;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (!dbconnected)
        return NULL;


    sql = "SELECT * FROM station WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (station)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return NULL;
    } else {
        res_p = mysql_store_result(&my_connection);
        if (res_p) {
            if (mysql_num_rows(res_p) > 0) {
//                cout << "ID  " << "Name " <<endl;
//                while (sqlRow = mysql_fetch_row(res_ptr)) {
//                    cout << sqlRow[0] << "  " << sqlRow[1] << endl;
//                }
                sql_row = mysql_fetch_row(res_p);
                ch = sql_row[1];
            } else {
                cout << "Cannot find the station ID!" << endl;
//                return NULL;
            }
            mysql_free_result(res_p);
        }
    }

    return ch;
}


/*****************
 *train
*****************/


int addTrain_mysql(char *num, int station, char *time) {
    int res;
    string sql;
    string snum = num;
    string stime = time;
    string sStation;

    ostringstream oss;
    oss << station;
    sStation = oss.str();

    if (num == NULL || station <= 0 || time == NULL) {
        cout<< "The train info is not complete!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "INSERT INTO train(trainNum,station,arrivalTime) VALUES(\"" + snum + "\"," + sStation + ",\"" + stime + "\")";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Insert error" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Insert train: " << num << " " << station << " " << time << " success!" << endl;
    return 1;
}

int deleteTrain_mysql(char *num) {
    int res;
    string sql;
    string snum = num;

    if (num == NULL) {
        cout<< "TrainNum cannot be empty!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "DELETE FROM train WHERE trainNum = \"" + snum + "\"";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Delete error (train)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Delete " << num << " success!" << endl;
    return 1;
}

int modifyTrain_mysql(int id,char *newNum,int newStation, char *newTime) {
    int res;
    string sql;
    string sID;
    string snum = newNum;
    string stime = newTime;
    string sStation;

    ostringstream oss;
    oss << newStation;
    sStation = oss.str();

    oss << id;
    sID = oss.str();

    if (newNum == NULL || newStation <= 0 || newTime == NULL) {
        cout<< "The train info is not complete!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "UPDATE station SET trainNum = \"" + snum + "\", station = " + sStation + ", time = \"" + stime  + "\" WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Update error (train)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Change to train: " << id << " " << newNum << " " << newStation << " " << newTime << " success!" << endl;
    return 1;
}

Train queryTrain_mysql(char *num) {
    int res;
    int id = 0;
    string sql;
    string snum = num;
    string str;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;
    Train train;
    QString qstr;
    QTime qtime;
    int i = 0;

    if (num == NULL) {
        cout<< "TrainNum cannot be empty!" << endl;
        return train;
    }

    if (!dbconnected) {
        return train;
    }

    sql = "SELECT * FROM train WHERE trainNum = \"" + snum + "\"";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (train)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return train;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
                while (sqlRow = mysql_fetch_row(res_ptr)) {
                    train.ID = QString(sqlRow[1]);
                    Station s;
                    s.ID = atoi(sqlRow[2]);
//                    char *name = queryStation_mysql(s.ID);
//                    s.name = QString(name);
                    s.name = QString(queryStation_mysql(s.ID));

                    qstr = QString(sqlRow[3]);
                    qtime = QTime::fromString(qstr);
                    train.appendStation(s, qtime);
                    i ++;

                    cout << i << sqlRow[0]<<sqlRow[1]<< sqlRow[2] << sqlRow[3] << endl;

                }
            } else {
                cout << "Cannot find the train!" << endl;
            }
            mysql_free_result(res_ptr);
        }
    }

    return train;
}

//MYSQL_ROW* queryTrainStation_mysql(char *num) {
//    int res;
//    int id = 0;
//    int i = 0;
//    string sql;
//    string snum = num;
//    string str;
//    MYSQL_RES *res_ptr;
//    MYSQL_ROW sqlRow;
//    MYSQL_ROW *rows;
//    Train train;
//    QString qstr;
//    QTime qtime;

//    if (num == NULL) {
//        cout<< "TrainNum cannot be empty!" << endl;
//        return rows;
//    }

//    if (!dbconnected) {
//        return rows;
//    }

//    sql = "SELECT * FROM train WHERE trainNum = \"" + snum + "\"";
//    const char *sql_c = sql.c_str();
//    res = mysql_query(&my_connection, sql_c);

//    if (res) {
//        cout << "Select error (train)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
//        return rows;
//    } else {
//        res_ptr = mysql_store_result(&my_connection);
//        if (res_ptr) {
//            if (mysql_num_rows(res_ptr) > 0) {
//                while (sqlRow = mysql_fetch_row(res_ptr)) {
//                    rows[i] = sqlRow;
//                    i++;
//                }
//            } else {
//                cout << "Cannot find the train!" << endl;
//            }
//            mysql_free_result(res_ptr);
//        }
//    }

//    return rows;
//}

//Train queryTrain_mysql(char *num) {
//    Train train;
//    QString qstr;
//    QTime qtime;

//    int i = 0;
//    MYSQL_ROW sqlRow;
//    MYSQL_ROW *rows;
//    rows = queryTrainStation_mysql(num);

//    if (rows == NULL)
//        return train;

//    while (sqlRow = rows[i]) {
//        train.ID = QString(sqlRow[1]);
//        Station s;
//        s.ID = atoi(sqlRow[2]);
////        char *name = queryStation_mysql(s.ID);
////        s.name = QString(name);
////        s.name = QString(queryStation_mysql(s.ID));

//        qstr = QString(sqlRow[3]);
//        qtime = QTime::fromString(qstr);

//        train.appendStation(s, qtime);

//        cout << sqlRow[0]<<sqlRow[1]<< sqlRow[2] << sqlRow[3] << endl;
//    }

//    return train;

//}



/*****************
 *ticket
*****************/


int addTicket_mysql(int ticketType, char *date, char *trainNum, int startStation, int arrivalStation, double price, char *seatNum, char *userID) {
    int res;
    string sql;
    string sticketType;
    string snum = trainNum;
    string sseat = seatNum;
    string sStart;
    string sArrival;
    string sdate = date;
    string sprice;
    string suserID = userID;

    ostringstream oss;
    oss << startStation;
    sStart = oss.str();
    oss.str("");

    oss << arrivalStation;
    sArrival = oss.str();
    oss.str("");

    oss << price;
    sprice = oss.str();
    oss.str("");

    oss << ticketType;
    sticketType = oss.str();

    if (!dbconnected)
        return 0;


    sql = "INSERT INTO ticket(ticketType,date,trainNum,startStation,arrivalStation,price,seatNum,userID) VALUES("
            + sticketType + ",\"" + sdate + "\",\"" + snum + "\"," + sStart + "," + sArrival + "," + sprice + ",\"" + sseat + "\",\"" + suserID + "\")";

    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Insert error(ticket)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Insert ticket: " << date << " " << trainNum << " " << seatNum << " success!" << endl;
    return 1;
}

int deleteTicket_mysql(int id) {
    int res;
    string sql;
    string sID;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (!dbconnected)
        return 0;


    sql = "DELETE FROM ticket WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Delete error (ticket)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Delete (ticket) " << id << " success!" << endl;
    return 1;
}

list<TrainTicket> queryTicket_mysql(char *userID) {
    int res;
    char *ch;
    string sql;
    string suserID = userID;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;
    list<TrainTicket> tickets;

    if (!dbconnected)
        return tickets;

    sql = "SELECT * FROM train.ticket ti, train.train tr1, train.train tr2 WHERE ti.userID = \"" + suserID
            + "\" and tr1.trainNum = ti.trainNum and tr2.trainNum = ti.trainNum and ti.startStation = tr1.station and ti.arrivalStation = tr2.station";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (ticket)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return tickets;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
                while (sqlRow = mysql_fetch_row(res_ptr)) {
                    TrainTicket ticket;

                    ticket.ID = atoi(sqlRow[0]);
                    ticket.trainNum = sqlRow[3];
                    ticket.date = sqlRow[2];
                    ticket.startStation = atoi(sqlRow[4]);
                    ticket.startTime = sqlRow[12];
                    ticket.arrivalStation = atoi(sqlRow[5]);
                    ticket.arrivalTime = sqlRow[16];
                    ticket.price = atof(sqlRow[6]);

                    tickets.push_back(ticket);

                }
            } else {
                cout << "Cannot find the ticket!" << endl;
//                return NULL;
            }
            mysql_free_result(res_ptr);
        }
    }

    return tickets;
}

TrainTicket queryTicketTypeDate_mysql(int id) {
    int res;
    char *ch;
    string sql;
    string sID;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;
    TrainTicket ticket;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (!dbconnected)
        return ticket;


    sql = "SELECT * FROM ticket WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (ticket)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return ticket;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
//                cout << "ID  " << "Name " <<endl;
//                while (sqlRow = mysql_fetch_row(res_ptr)) {
//                    cout << sqlRow[0] << "  " << sqlRow[1] << endl;
//                }
                sqlRow = mysql_fetch_row(res_ptr);

                ticket.ticketType = atoi(sqlRow[1]);
                ticket.date = QString(sqlRow[2]);
                ticket.trainNum = QString(sqlRow[3]);
                ticket.startStation = atoi(sqlRow[4]);
                ticket.arrivalStation = atoi(sqlRow[5]);
                ticket.price = atof(sqlRow[6]);
                ticket.userID = QString(sqlRow[8]);

            } else {
                cout << "Cannot find te ticket ID!" << endl;
//                return NULL;
            }
            mysql_free_result(res_ptr);
        }
    }

    return ticket;
}

/*****************
 *ticketType
*****************/

int addTicketType_mysql(char *trainNum, int startStation, int arrivalStation, int seatNum, double price) {
    int res;
    string sql;
    string snum = trainNum;
    string sseat;
    string sStart;
    string sArrival;
    string sprice;

    ostringstream oss;
    oss << startStation;
    sStart = oss.str();
    oss.str("");

    oss << arrivalStation;
    sArrival = oss.str();
    oss.str("");

    oss << seatNum;
    sseat = oss.str();
    oss.str("");

    oss << price;
    sprice = oss.str();


    if (!dbconnected)
        return 0;


    sql = "INSERT INTO ticketType(trainNum,startStation,arrivalStation,seatNum,price) VALUES(\""
        + snum + "\"," + sStart + "," + sArrival + "," + sseat + "," + sprice + ")";

    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Insert error(ticketType)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Insert ticketType: " << trainNum << " " << startStation << " to " << arrivalStation << " success!" << endl;
    return 1;
}

int deleteTicketType_mysql(int id) {
    int res;
    string sql;
    string sID;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (!dbconnected)
        return 0;


    sql = "DELETE FROM ticketType WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Delete error (ticketType)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Delete (ticketType) " << id << " success!" << endl;
    return 1;
}

Ticket queryTicketType_mysql(int id) {
    int res;
    char *ch;
    string sql;
    string sID;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;
    Ticket ticket;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (!dbconnected)
        return ticket;


    sql = "SELECT * FROM ticketType WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (ticketType)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return ticket;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
//                cout << "ID  " << "Name " <<endl;
//                while (sqlRow = mysql_fetch_row(res_ptr)) {
//                    cout << sqlRow[0] << "  " << sqlRow[1] << endl;
//                }
                sqlRow = mysql_fetch_row(res_ptr);

                ticket.ID = QString(sqlRow[0]);
                ticket.trainNum = QString(sqlRow[1]);
                ticket.startStationIndex = atoi(sqlRow[2]);
                ticket.arrivalStationIndex = atoi(sqlRow[3]);
                ticket.seatNum = atoi(sqlRow[4]);
                ticket.price = atof(sqlRow[5]);

            } else {
                cout << "Cannot find the ticketType ID!" << endl;
//                return NULL;
            }
            mysql_free_result(res_ptr);
        }
    }

    return ticket;
}

/*****************
 *ticketRecord
*****************/


int addTicketRecord_mysql(int ticketType, char *date, int ticketNum, int restNum) {
    int res;
    string sql;
    string sdate = date;
    string stype;
    string sticketNum;
    string srestNum;

    ostringstream oss;
    oss << ticketType;
    stype = oss.str();
    oss.str("");

    oss << ticketNum;
    sticketNum = oss.str();
    oss.str("");

    oss << restNum;
    srestNum = oss.str();


    if (!dbconnected)
        return 0;


    sql = "INSERT INTO ticketRecord(ticketType,date,ticketNum,restNum) VALUES("
        + stype + ",\"" + sdate + "\"," + sticketNum + "," + srestNum + ")";

    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Insert error(ticketRecord)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Insert ticketRecord: " << ticketType << " " << date << " success!" << endl;
    return 1;
}

int deleteTicketRecord_mysql(int id) {
    int res;
    string sql;
    string sID;

    ostringstream oss;
    oss << id;
    sID = oss.str();

    if (!dbconnected)
        return 0;


    sql = "DELETE FROM ticketRecord WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Delete error (ticketRecord)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Delete (ticketRecord) " << id << " success!" << endl;
    return 1;
}

int modifyTicketRecord_mysql(int id, int restNum){
    int res;
    string sql;
    string sID;
    string srestNum;

    ostringstream oss;
    oss << id;
    sID = oss.str();
    oss.str("");

    oss << restNum;
    srestNum = oss.str();

    if (!dbconnected)
        return 0;


    sql = "UPDATE ticketRecord SET restNum = " + srestNum + " WHERE id = " + sID;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Update error (ticketRecord)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Change " << "to " << id << " " << restNum << " success!" << endl;
    return 1;
}

list<TicketRecord> queryTicketRecord_mysql(int start, int arrival, char *date){
    int res;
    char *ch;
    string sql;
    string sStart;
    string sArrival;
    string sdate = date;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;
    list<TicketRecord> tickets;

    ostringstream oss;
    oss << start;
    sStart = oss.str();
    oss.str("");

    oss << arrival;
    sArrival = oss.str();

    if (!dbconnected)
        return tickets;

    cout << sdate << " " << sStart << " " << sArrival << endl;

   sql = "SELECT * FROM ticketRecord r,ticketType t WHERE r.date = \"" + sdate
       + "\" and r.ticketType = t.id and t.startStation = " + sStart + " and t.arrivalStation = " + sArrival;
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (ticketRecord)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return tickets;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
                while (sqlRow = mysql_fetch_row(res_ptr)) {
                    TicketRecord ticketRecord;
                    ticketRecord.ID = atoi(sqlRow[0]);
                    ticketRecord.ticketType = atoi(sqlRow[1]);
                    ticketRecord.date = sqlRow[2];
                    ticketRecord.ticketNum = atoi(sqlRow[3]);
                    ticketRecord.restNum = atoi(sqlRow[4]);
                    ticketRecord.trainNum = sqlRow[6];
                    ticketRecord.startStation = atoi(sqlRow[7]);
                    ticketRecord.arrivalStation = atoi(sqlRow[8]);
                    ticketRecord.price = atof(sqlRow[10]);
                    tickets.push_back(ticketRecord);
                }

            } else {
                cout << "Cannot find the ticketType ID!" << endl;
//                return NULL;
            }
            mysql_free_result(res_ptr);
        }
    }

    return tickets;
}

TicketRecord queryRestNum_mysql(int ticketType, char *date) {
    int res;
    char *ch;
    string sql;
    string sticketType;
    string sdate = date;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;
    TicketRecord ticketRecord;
    ticketRecord.restNum = 0;

    ostringstream oss;
    oss << ticketType;
    sticketType = oss.str();

    if (!dbconnected)
        return ticketRecord;

    sql = "SELECT * FROM ticketRecord r,ticketType t WHERE r.date = \"" + sdate
            + "\" and r.ticketType = " + sticketType + " and r.ticketType = t.id";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (ticketRecord)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return ticketRecord;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
//                cout << "ID  " << "Name " <<endl;
//                while (sqlRow = mysql_fetch_row(res_ptr)) {
//                    cout << sqlRow[0] << "  " << sqlRow[1] << endl;
//                }
                sqlRow = mysql_fetch_row(res_ptr);

                ticketRecord.ID = atoi(sqlRow[0]);
                ticketRecord.ticketType = atoi(sqlRow[1]);
                ticketRecord.date = sqlRow[2];
                ticketRecord.ticketNum = atoi(sqlRow[3]);
                ticketRecord.restNum = atoi(sqlRow[4]);
                ticketRecord.trainNum = sqlRow[6];
                ticketRecord.startStation = atoi(sqlRow[7]);
                ticketRecord.arrivalStation = atoi(sqlRow[8]);
                ticketRecord.price = atof(sqlRow[10]);

            } else {
                cout << "Cannot find the ticketRecord!" << endl;
//                return NULL;
            }
            mysql_free_result(res_ptr);
        }
    }

    return ticketRecord;
}


/*****************
 *user
*****************/

int addUser_mysql(char *id, char *name, char *password, char *idNum) {
    int res;
    string sql;
    string sID = id;
    string sname = name;
    string spassword = password;
    string sidNum = idNum;

    if (!dbconnected)
        return 0;


    sql = "INSERT INTO uesr(userid,name,password,idNum) VALUES(\""
        + sID + "\",\"" + sname + "\",\"" + spassword + "\",\"" + sidNum + "\")";

    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Insert error(user)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Insert user: " << id << " success!" << endl;
    return 1;
}

int deleteUser_mysql(char *id) {
    int res;
    string sql;
    string sID = id;

    if (id = NULL) {
        cout << "Userid cannot be empty!" << endl;
    }

    if (!dbconnected)
        return 0;

    sql = "DELETE FROM user WHERE userid = \"" + sID + "\"";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Delete error (user)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Delete (user) " << id << " success!" << endl;
    return 1;
}

int modifyUser_mysql(char *id, char *newName, char *newPassword){
    int res;
    string sql;
    string sID = id;
    string sname = newName;
    string spassword = newPassword;

    if (id == NULL) {
        cout<< "User id cannot be empty!" << endl;
        return 0;
    }

    if (newName == NULL) {
        cout<< "User name cannot be empty!" << endl;
        return 0;
    }

    if (newPassword == NULL) {
        cout<< "Password cannot be empty!" << endl;
        return 0;
    }

    if (!dbconnected)
        return 0;


    sql = "UPDATE user SET name = \"" + sname + "\", password = \"" + spassword + "\" WHERE userid = \"" + sID + "\"";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Update error (user)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return 0;
    }

    cout << "Change to " << id << " " << newName << " " << newPassword << " success!" << endl;
    return 1;
}

User queryUser_mysql(char *id) {
    int res;
    char *ch;
    string sql;
    string sID = id;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlRow;
    User user;

    if (!dbconnected)
        return user;

    cout << id << endl;

    sql = "SELECT * FROM user WHERE userid = \"" + sID + "\"";
    const char *sql_c = sql.c_str();
    res = mysql_query(&my_connection, sql_c);

    if (res) {
        cout << "Select error (user)" << mysql_errno(&my_connection) << ", " << mysql_error(&my_connection) << endl;
        return user;
    } else {
        res_ptr = mysql_store_result(&my_connection);
        if (res_ptr) {
            if (mysql_num_rows(res_ptr) > 0) {
//                cout << "ID  " << "Name " <<endl;
//                while (sqlRow = mysql_fetch_row(res_ptr)) {
//                    cout << sqlRow[0] << "  " << sqlRow[1] << endl;
//                }
                sqlRow = mysql_fetch_row(res_ptr);

                user.ID = QString(sqlRow[1]);
                user.name = QString(sqlRow[2]);
                user.password = QString(sqlRow[3]);
                user.idNum = QString(sqlRow[4]);

            } else {
                cout << "Cannot find the User ID!" << endl;
//                return NULL;
            }
            mysql_free_result(res_ptr);
        }
    }

    return user;
}





